#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2021-08-30 08:07
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.7.3
# 功能     ：
import functools
import traceback
from concurrent.futures.thread import ThreadPoolExecutor

import sqlalchemy
from sqlalchemy import *
import logging
import time_ext, os
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, Session, sessionmaker
import pymysql, pymssql, pyodbc, cx_Oracle, psycopg2, sqlite3

BASE = declarative_base()


# 类方法异常装饰器
def try_error_class(func):
    @functools.wraps(func)
    def wrapper(self, *args, **kwargs):
        try:
            if args:
                res = func(self, *args, **kwargs)
            else:
                res = func(self)
            return res
        except Exception as e:
            error_info = str(e)
            if "for key 'PRIMARY'" in error_info:
                logging.error("数据已存在，主键冲突，请检测主键及数据，异常：{}".format(e))
            else:
                logging.error("异常：{}".format(e))
                # traceback.format_exc()

    return wrapper


class Connect(object):
    """docstring for Connect"""

    def __init__(self, config):
        super(Connect, self).__init__()
        self.log = logging.getLogger('main.sqlalchemy')
        self.config = config
        self.type = self.config.get('type')
        self.engine = self.get_engine()
        self.session = scoped_session(sessionmaker(bind = self.engine))

    # 获取连接
    def get_connect(self):
        return self.engine.connect()

    # 获取引擎
    def get_engine(self):
        mode = self.config.get('mode')
        _type = self.type.lower() if self.type else ""
        if mode in ("连接池", 0, '0'):
            return self._get_engine(_type, True)
        if mode in ('短连接', 1, '1'):
            return self._get_engine(_type)
        if mode in ('ODBC', 2, '2'):
            return self._get_odbc(_type)

    # 获取短、连接池
    def _get_engine(self, _type, pool = None):
        file_path = self.get_file_path()
        if _type == "sqlite":
            driver = "sqlite://" if self.config.get('memory') else "sqlite:///{}".format(file_path)
            engine = create_engine(driver, echo = True)
            return engine

        elif _type == "access":
            engine = create_engine('access:///{}'.format(file_path))
            return engine

        elif _type == "postgresql":
            self.config['port'] = self.config.get('port', 5432)
            driver = "postgresql+psycopg2"

        elif _type == "mysql":
            self.config['port'] = self.config.get('port', 3306)
            driver = "mysql+pymysql"

        elif _type == "oracle":
            self.config['port'] = self.config.get('port', 1521)
            driver = "oracle+cx_oracle"

        elif _type in ("sql server", "sqlserver", "mssql"):
            self.config['port'] = self.config.get('port', 1433)
            driver = "mssql+pymssql"

        else:
            driver = ""

        url = "{}://{username}:{password}@{host}:{port}/{database}".format(driver, **self.config)
        if pool:
            self.log.info("初始连接池")
            engine = create_engine(
                    url,
                    max_overflow = int(self.config.get('maxconnections', 5)),  # 超过连接池大小外最多创建的连接
                    pool_size = int(self.config.get('mincached', 2)),  # 连接池大小
                    pool_timeout = 10,  # 池中没有线程最多等待的时间，否则报错
                    pool_recycle = 15,  # 多久之后对线程池中的线程进行一次连接的回收（重置）
                    pool_pre_ping = True,  # 悲观机制检测数据库
                    # echo = True
                    )
        else:
            self.log.info("初始短连接")
            engine = create_engine(url, echo = True)
        return engine

    # 获取odbc连接
    def _get_odbc(self, _type):
        if _type == "sqlite":
            return self._get_engine(_type)

        elif _type == "access":
            driver = "access+pyodbc"

        elif _type == "postgresql":
            driver = "postgresql+pyodbc"

        elif _type == "mysql":
            driver = "mysql+pyodbc"

        elif _type == "oracle":
            return self._get_engine(_type)

        elif _type in ("sql server", "sqlserver", "mssql"):
            driver = "mssql+pyodbc"

        else:
            driver = ""
        self.log.info("初始连接ODBC")
        engine = create_engine("{}://{username}:{password}@{dsn}".format(driver, **self.config))
        return engine

    # 获取带时间的数据库库
    def get_file_path(self):

        path = self.config.get('databasepath')
        file_path, file_name = os.path.split(path)
        # 时间格式串
        ext = ""
        if self.config.get('format'):
            now = time_ext.get_datetime()
            y = "{:0>4}".format(now.year)
            m = "{:0>2}".format(now.month)
            d = "{:0>2}".format(now.day)
            ext = self.config.get('format').replace('Y', y).replace('m', m).replace('d', d)
        else:
            return path

        # 去除时间格式
        size = len(ext)
        if self.config.get('left') != '0':
            file_name = file_name[size:]
        if self.config.get('right') != '0':
            name, _ext = os.path.splitext(file_name)
            name = name[:len(name) - size]
            file_name = name + _ext
        # 附加左边时间格式
        if self.config.get('left') != '0':
            path = "{}/{}{}".format(file_path, ext, file_name)
            return path
        # 附加右边时间格式
        if self.config.get('right') != '0':
            name, _ext = os.path.splitext(file_name)
            path = "{}/{}{}{}".format(file_path, name, ext, _ext)
            return path
        # 非时间格式
        return path

    @try_error_class
    def loadSql(self, sql, is_dict = None,is_obj=None):
        session = self.session
        data =  session.execute(text(sql))
        if is_obj :
            return data
        if not is_dict:
            return list(data)
        keys = data.keys()
        dict_data = [{k: row[i] for i, k in enumerate(keys)} for row in data]
        session.remove()
        return dict_data

    @try_error_class
    def execSql(self, sql, values = None):
        session = self.session
        session.autocommit = True
        if not values:
            result = session.execute(sql)

        else:
            result = session.execute(sql, values)

        session.remove()
        return result.rowcount


if __name__ == '__main__':
    import ticket_base
    from threading import Thread

    import time

    tt = ticket_base.get_chehao_info()
    config = {

            'mode': '连接池',
            # 'mode': "ODBC",
            'type': 'MYsql',
            'name': 'ics',
            'host': '127.0.0.1',
            'dsn': 'MSSQL',
            'mincached': 10,
            # 'port': 3306,
            'username': 'ics',
            'password': '123456',
            'database': 'test',
            'databasepath': r'D:\CODE\PYTHON\DA_V5\app_all\config.db'
            # 'charset': 'gbk'
            }
    app = Connect(config)
    res = app.execSql("insert into t_540_chehao (f_id,f_chehao)values (1234,777777)")
    print(res)
    # res2 =Session(app.engine).execute(tt.insert(),{'F_ID':5556})
    # print(res2.all())

    # table = ticket_base.get_ticket_main("QC")
    # ticket_base.get_ticket_main("", "ZY")
    # ticket_base.get_ticket_sub("QC", "ZY", index = 2)
    # ticket_base.get_truck_info()

    # ticket_base.get_unload_info()
    # ticket_base.get_unload()
    # conn = app.get_connect()
    # data = conn.connect().execute(text("select * from t_540_chehao "))
    # # print(dir(data))
    # print(data.keys(), data.first())
    # res = ticket_base.base.metadata.create_all(conn)
    # print(res)
    # print(tt)
    # with Session(app.engine) as session:
    #     xx=session.query(tt).all()
    #
    #     for i ,row in enumerate(xx):
    #         print(i,row,dir(row))
    #         print(i,row,row.F_ID)
    # pool = ThreadPoolExecutor(max_workers = 10)
    # while True:
    #     # with Session(app.engine) as conn:
    #         sql = "SELECT * FROM T_540_CHEHAO"
    #         # data =app.loadSql(sql)
    #         # print(data)
    #
    #         for i in range(500):
    #             # result = pool.submit(conn.query,tt)
    #             result = pool.submit(app.loadSql,sql,True)
    #             print(i, result.result())
    #
    #         print(int(time.time()))
    #         time.sleep(1)